package com.eam.service;

import com.yy.base.common.dao.Dao;
import com.yy.base.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class SparePartsCollarService {
	@Autowired
	private Dao dao;
	
	//加载所有的设备备品配件物料信息
	public List<Map<String,Object>> getSparepartsList() {
		
		return dao.query("select wl.Eam_Spareparts_id as WLID,   "
				+ " wl.code as wlcode, "
				+ " wl.name as wlname, "
				+ " wl.model, gx.name as gxname ,  "
				+ "(case wl.company when '10' then  '对'  when '20' then '个' when '30' then '根'  when '40' then  '盒' when '50' then '卷' when '60' then  '块' when '70' then '片' when '80' then  '套'   else ''  end) as company,"
				+ "  wl.safety_stock as annumbers,  wl.cycle "
				+ "from Eam_Spareparts wl"
				+ " left join emes_processopr gx on wl.emes_processopr_id = gx.emes_processopr_id");
	}
	
	//加载所有的设备备品配件物料信息（加了三个筛选条件的）
	public List<Map<String,Object>> getSparepartsLists(String Epcode,String wlnames,String models) {
			
		String sql = "select wl.Eam_Spareparts_id as WLID,   "
				+ " wl.code as wlcode, "
				+ " wl.name as wlname, "
				+ " wl.model, gx.name as gxname ,  "
				+ "(case wl.company when '10' then  '对'  when '20' then '个' when '30' then '根'  when '40' then  '盒' when '50' then '卷' when '60' then  '块' when '70' then '片' when '80' then  '套'   else ''  end) as company,"
				+ "  wl.safety_stock as annumbers,  wl.cycle "
				+ "from Eam_Spareparts wl"
				+ " left join emes_processopr gx on wl.emes_processopr_id = gx.emes_processopr_id where 1 = 1";
		
		String where = "";
		
		if(Epcode != null && !Epcode.equals("")){
			where += " and (wl.code like '%"+Epcode+"%' ";
		}
		if(wlnames != null && !wlnames.equals("")){
			where += (where.length() > 0 ? " or " : " and(") + " wl.name like '%"+wlnames+"%' ";
		}
		
		if(models != null && !models.equals("")){
			where += (where.length() > 0 ? " or " : " and(") + " wl.model like '%"+models+"%' ";
		}
	
		
		return dao.query(sql + (where.length() > 0 ? where + ")" : ""));
					
	}
	
	//加载组织机构(组织机构不属于车间的)
	public List<Map<String,Object>> getEquipmenadorgtList() {
		return dao.query("select name , ad_org_id as ORGID from ad_org where ad_orgtype_id = (select ad_orgtype_id from ad_orgtype where name = '车间')");
		
	}
	
	//加载工序
	public List<Map<String,Object>> getEquipmenprocessList() {
		return dao.query("select name ,emes_processopr_id as PROCESSID from emes_processopr");
	}
	
	//加载设备备品配件仓库下的储位
	public List<Map<String,Object>> getEquipmetStoragesList(String wlcode) {
		
	
		return dao.query("select barcode , m_locator_ID AS KWID "
				+ "from M_locator where M_locator_id in  "
				+ "(select M_locator_id   from Eam_Spareparts_Collar ckjl  where  ckjl.Eam_Spareparts_id = (select Eam_Spareparts_id from Eam_Spareparts where code = ? ) ) " , wlcode);
		
		//return dao.query("select BARCODE ,m_locator_ID AS KWID from m_locator  where m_warehouse_id = 'FADC875A87544C9D80168957068700D1' ");
	}
	
	//加载所有的供应商
	public List<Map<String,Object>> getEquipmetSupplierList(String wlcode) {
		
		return dao.query("select name  as gysname , emes_vendor_id as gysId from emes_vendor "
				+ " where emes_vendor_id in  "
				+ "(select emes_vendor_id from Eam_Spareparts_Collar ckjl  where ckjl.Eam_Spareparts_id  = "
				+ "(select Eam_Spareparts_id  from Eam_Spareparts where  code = ? ) ) ",wlcode);
		
		//return dao.query(" select name as gysname , emes_vendor_id as gysId from emes_vendor  ");
	}
	
	//加载设备备品配件物料的库存数量
	public List<Map<String,Object>> getSumMateriels(String wlcode) {
		return dao.query("select ((select sum(jl.numbers)   "
				+ "from Eam_Spareparts_Collar jl "
				+ " left join Eam_Spareparts  wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id "
				+ " where jl.type = 'added' and wl.code = '"+wlcode+"') -   "
				+ "(select NVL(sum(jl.numbers), 0)"
				+ "  from Eam_Spareparts_Collar jl "
				+ "  left join Eam_Spareparts  wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id "
				+ "  where jl.type = 'Out' and wl.code = '"+wlcode+"' and jl.status != 'Notthrough' )) as qty  from dual " );
				
	}
	
	//查询物料编码是否存在
	public List<Map<String,Object>> getEquipmenmateriel(String WULCODES) {
		return dao.query("select wl.Eam_Spareparts_id as WLID , wl.name as WLNAME ,wl.code as WLCODE , "
				          + " wl.m_locator_id as KWID , wl.company as COMPANY ,WL.PRICE ,WL.NUMBERS AS NUMBERS ,"
				          + " WL.SAFETY_STOCK AS ANNUMBERS  from Eam_Spareparts wl where code =  ? ",WULCODES);
	}
	
	//加载设备编码
	public List<Map<String,Object>> getEquipmentPlanList(String ORGNAME  ,String GXNAME) {
		
		
		return dao.query("select code , name ,Eam_Facilities_id as SBID from Eam_Facilities "
				+ " where Eam_Facilities_id in (select Eam_Facilities_id from Emes_Process_Facilities "
				+ " where emes_process_id =(select emes_process_id from emes_process where emes_processopr_id = ? "
				+ " and emes_process_route_id = (select emes_process_route_id from emes_process_route where ad_org_id = ?)))", GXNAME , ORGNAME);
	}
	
	//加载领用的物料在制定的库位上加上供应商还剩下多少库存数量
	public List<Map<String,Object>> getEquipmentVendorList(String KWNAME , String WLCODE) {
			
		return dao.query("select ((select sum(jl.numbers) as sumnumbers  from Eam_Spareparts_Collar jl  "
				+ "  left join Eam_Spareparts  wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id	  "
				+ "  left join emes_vendor gys on gys.emes_vendor_id = jl.emes_vendor_id "
				+ "	 left join m_locator cw on cw.m_locator_id = jl.m_locator_id "
				//+ "  where jl.type = 'added' and wl.code = '"+WLCODE+"' and gys.emes_vendor_id = '"+GYSIDS+"' and cw.m_locator_id = '"+KWNAME+"') - "
				+ "  where jl.type = 'added' and wl.code = '"+WLCODE+"' and cw.m_locator_id = '"+KWNAME+"') - "
				+ "	 (select NVL(sum(jl.numbers), 0) from Eam_Spareparts_Collar jl	left join Eam_Spareparts  wl on jl.Eam_Spareparts_id = wl.Eam_Spareparts_id "
				+ "	 left join emes_vendor gys on gys.emes_vendor_id = jl.emes_vendor_id	left join m_locator cw on cw.m_locator_id = jl.m_locator_id		"
				//+ "  where jl.type = 'Out' and wl.code = '"+WLCODE+"' and gys.emes_vendor_id = '"+GYSIDS+"' and cw.m_locator_id = '"+KWNAME+"'  and jl.status != 'Notthrough' )) as qty  from dual ");
				+ "  where jl.type = 'Out' and wl.code = '"+WLCODE+"'  and cw.m_locator_id = '"+KWNAME+"'  and jl.status != 'Notthrough' )) as qty  from dual ");

	}
	
	//查询当前出库的物料的价格
	public List<Map<String,Object>> getEquipmentQtys(String MATERIELIDS, String CHUWEIS) {
				
		return dao.query("select jl.price  as PRICES  from Eam_Spareparts_Collar jl  "
				+ "where jl.Eam_Spareparts_id = '"+MATERIELIDS+"'   and jl.m_locator_id = '"+CHUWEIS+"'  "
				+ " and jl.created =  (select min(timejl.created)   from Eam_Spareparts_Collar timejl "
				+ " where timejl.Eam_Spareparts_id =   '"+MATERIELIDS+"'   and timejl.m_locator_id = '"+CHUWEIS+"') ");
			
	}
	
	/*//设备备品配件领用后扣除库存数量
	@SuppressWarnings("unchecked")
	public boolean getUpdaeteSpareParts(int numbers , String ids){
		boolean result = false;
		
		String sql =  "UPDATE Eam_Spareparts SET Numbers = ?  WHERE Eam_Spareparts_id = ?";
  		
		result = dao.executeSql(sql, numbers , ids) > 0;
		
		return result;
	}*/
	
	//查询CREATEBY和UPDATEBY的ID值
	public List<Map<String,Object>> getEquipmentputCreateby(String userIdtime){
		return dao.query("select CREATEDBY as CREATEDBY, UPDATEDBY as UPDATEDBY from ad_user where ad_user_id = ?",userIdtime);
	}
	
	//设备备品配件领用存入出入库记录表中
	@SuppressWarnings("unchecked")
	public boolean insertSparePartsList(String MATERIELIDS,String LOCATOR,String COMPANYS,int COLLARs,int price,String GONGXUID,String SHEBEIID,Date time,String userIdtime,String type,String status,String BEIZHUS,String adclientId,String ORGIDS,String createBy,String updatedBy,int KUSHULIANGs,String GYSNAMES) {
		boolean result = false;
		String uuid = StringUtil.getUUID();
		String sql =  "INSERT INTO Eam_Spareparts_Collar (Eam_Spareparts_COLLAR_ID,Eam_Spareparts_ID,M_LOCATOR_ID,NUMBERS,PRICE,Emes_PROCESSOPR_ID,Eam_Facilities_ID,COLLAR_TIME,AD_USER_ID,TYPE,STATUS,REMARKS,COMPANY,AD_ORG_ID,AD_CLIENT_ID,ISACTIVE,CREATED,CREATEDBY,UPDATED,UPDATEDBY,STOCK_NUMBERS,Emes_Vendor_id) " +
					"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
		result = dao.executeSql(sql, uuid,MATERIELIDS,LOCATOR,COLLARs,price,GONGXUID,SHEBEIID,time,userIdtime,type,status,BEIZHUS,COMPANYS,ORGIDS,adclientId,"Y",time,createBy,time,updatedBy,KUSHULIANGs,GYSNAMES) > 0;
			
		return result;
		}

}
